﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOQLDV;
using System.Data;
using System.Data.OleDb;

namespace DAOQLDV
{
    public class DAOTinhThanh
    {
        public static List<DTOTinhThanh> LayDanhSachTinhThanh()
        {
            List<DTOTinhThanh> dsTinhThanh = new List<DTOTinhThanh>();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From TinhThanh";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                while (dr.Read())
                {
                    DTOTinhThanh dtoTT = new DTOTinhThanh();
                    dtoTT.MaTinhThanh = (int)dr["MaTinhThanh"];
                    dtoTT.TenTinhThanh = (String)dr["TenTinhThanh"];
                    dsTinhThanh.Add(dtoTT);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dsTinhThanh;
        }
        public static DTOTinhThanh LayThongTinTinhThanh(String tenTinhThanh)
        {
            DTOTinhThanh dtoTinhThanh = new DTOTinhThanh();
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select * From TinhThanh Where TenTinhThanh like '" + tenTinhThanh + "'";
                OleDbDataReader dr = da.ExecuteQuery(sql);
                if (dr.Read())
                {
                    dtoTinhThanh.MaTinhThanh = (int)dr["MaTinhThanh"];
                    dtoTinhThanh.TenTinhThanh = dr["TenTinhThanh"].ToString();
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return dtoTinhThanh;
        }
                
        public static bool CapNhatTinhThanh(DTOTinhThanh dtoTT)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Update TenTinhThanh Set TenTinhThanh = '" + dtoTT.TenTinhThanh + "' Where MaTenTinhThanh = " + dtoTT.MaTinhThanh;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }        
        public static bool XoaTinhThanh(DTOTinhThanh dtoTT)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Delete From TenTinhThanh Where MaTenTinhThanh = " + dtoTT.MaTinhThanh;
                da.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }      
        public static bool ThemTinhThanh(DTOTinhThanh dtoTT)
        {
            bool kq = false;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                da.BeginTran();
                String sql = "Insert Into TenTinhThanh(TenTinhThanh) Values ('" + dtoTT.TenTinhThanh + "')";
                da.ExecuteNonQuery(sql);
                sql = "Select @@IDENTITY";
                dtoTT.MaTinhThanh = (int)da.ExecuteScalar(sql);
                DAOTuKhoaTinhThanh.ThemTuKhoaTinhThanh(dtoTT, da);
                da.CommitTran();
                kq = true;
            }
            catch (Exception ex)
            {
                da.RollBackTran();
                throw ex;
            }
            finally 
            {
                da.Close();
            }
            return kq;
        }
        public static int LayMaTinhThanh(String tenTinhThanh)
        {
            int kq = 0;
            DataAccess da = new DataAccess();
            try
            {
                da.Open();
                String sql = "Select MaTinhThanh From TinhThanh Where TenTinhThanh Like '" + tenTinhThanh + "'";
                kq = int.Parse("0" + da.ExecuteScalar(sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Close();
            }
            return kq;
        }
    }
}
